package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.CustomInfo;
import model.User;
import utils.JdbcUtil;
import utils.RequestUtil;
import utils.msg.PollingQueryRunnable;

public class CustomInfoDao {
	public List<CustomInfo> getCustomInfoListByTid(String tId){
		String sql = "SELECT ueId,fid,fieldName,condition,value,[desc]"+
				" FROM t_fields as tf ,t_eventFieldsCondition as te "+
				" where tf.id = te.fId and tId = "+tId;
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		List<CustomInfo> customInfoList = null;
		try {
			con = JdbcUtil.getCon();
			customInfoList = new ArrayList<CustomInfo>();
			 pstmt = con.prepareStatement(sql);
			 rs = pstmt.executeQuery();
			while(rs.next()){
				CustomInfo ci = new CustomInfo();
				ci.setfId(rs.getInt("fId"));
				ci.setFieldName(rs.getString("fieldName"));
				ci.setCondition(rs.getString("condition"));
				ci.setValue(rs.getString("value"));
				ci.setDesc(rs.getString("desc"));
				customInfoList.add(ci);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return customInfoList;
		
	}
	
	public List<CustomInfo> getCustomInfoListByUeid(String ueId){
		String sql = "SELECT ueId,fid,fieldName,condition,value"+
				" FROM t_fields as tf ,t_eventFieldsCondition as te "+
				" where tf.id = te.fId and ueId = "+ueId;
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		List<CustomInfo> customInfoList = null;
		try {
			con = JdbcUtil.getCon();
			customInfoList = new ArrayList<CustomInfo>();
			 pstmt = con.prepareStatement(sql);
			 rs = pstmt.executeQuery();
			while(rs.next()){
				CustomInfo ci = new CustomInfo();
				ci.setfId(rs.getInt("fId"));
				ci.setFieldName(rs.getString("fieldName"));
				ci.setCondition(rs.getString("condition"));
				ci.setValue(rs.getString("value"));
				customInfoList.add(ci);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return customInfoList;
		
	}
	
	public List<CustomInfo> getCustomInfoList(){
		
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		List<CustomInfo> tableList = null;
		try {
			con = JdbcUtil.getCon();
			String sql = " select u.id uId, u.username, u.phoneNumber,u.email,dbs.dbName,t.tableName, et.typeName, ue.pollingTime, "+
  " ue.timeSpan, ue.id ueId ,ue.tId tId, [desc],type from  t_userEvent ue,  t_user u, t_eventType et ,t_dbs dbs,t_tables t where "+
   " ue.uId = u.id and ue.etId = et.id and t.id = ue.tId and dbs.id = t.dbId ";//desc是关键字，需要使用[]括起来
			tableList = new ArrayList<CustomInfo>();
			 pstmt = con.prepareStatement(sql);
			 rs = pstmt.executeQuery();
			while(rs.next()){
				CustomInfo ci = new CustomInfo();
				ci.setDesc(rs.getString("desc"));
				ci.setuId(rs.getInt("uId"));
				ci.setUeId(rs.getInt("ueId"));
				ci.setUsername(rs.getString("username"));
				ci.setPhoneNumber(rs.getString("phoneNumber"));
				ci.setDbName(rs.getString("dbName"));
				ci.setTableName(rs.getString("tableName"));
				ci.setEmail(rs.getString("email"));
			//	ci.setfId(rs.getInt("fId"));
				ci.settId(rs.getInt("tId"));
			//	ci.setFieldName(rs.getString("fieldName"));
				ci.setEventType(rs.getString("typeName"));
				ci.setPollingTime((int)(rs.getInt("pollingTime")/1000.0));
				ci.setTimeSpan((int)(rs.getInt("timeSpan")/1000));
				ci.setType(rs.getString("type"));
			//	ci.setCondition(rs.getString("condition"));
			//	ci.setValue(rs.getFloat("value"));
				tableList.add(ci);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return tableList;
	}
	
	public List<CustomInfo> getStartInfoList(Map<Integer, PollingQueryRunnable> map){
			
			Connection con =null;  
	        PreparedStatement pstmt = null;  
	        ResultSet rs = null;
			List<CustomInfo> tableList = null;
			try {
				con = JdbcUtil.getCon();
				String sql = "select u.id uId, u.username, u.phoneNumber,u.email,dbs.dbName,t.tableName, f.id fId, f.fieldName,et.typeName, ue.pollingTime, ue.timeSpan, ue.id ueId , efc.condition, efc.value from t_eventFieldsCondition efc, t_userEvent ue, t_fields f, t_user u, t_eventType et ,t_dbs dbs,t_tables t where ue.id = efc.ueId and ue.uId = u.id and ue.etId = et.id and efc.fId = f.id and dbs.id = t.dbId and t.id = f.tId";
				tableList = new ArrayList<CustomInfo>();
				 pstmt = con.prepareStatement(sql);
				 rs = pstmt.executeQuery();
				while(rs.next()){
					if(map.containsKey(rs.getInt("ueId"))){
					CustomInfo ci = new CustomInfo();
					ci.setuId(rs.getInt("uId"));
					ci.setUeId(rs.getInt("ueId"));
					ci.setUsername(rs.getString("username"));
					ci.setPhoneNumber(rs.getString("phoneNumber"));
					ci.setDbName(rs.getString("dbName"));
					ci.setTableName(rs.getString("tableName"));
					ci.setEmail(rs.getString("email"));
					ci.setfId(rs.getInt("fId"));
					ci.setFieldName(rs.getString("fieldName"));
					ci.setEventType(rs.getString("typeName"));
					ci.setPollingTime(rs.getInt("pollingTime"));
					ci.setTimeSpan(rs.getInt("timeSpan"));
					ci.setCondition(rs.getString("condition"));
					ci.setValue(rs.getString("value"));
					tableList.add(ci);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				JdbcUtil.closeAll(rs, pstmt,con);
			}
			return tableList;
		}
	
	public List<CustomInfo> getnonStartInfoList(Map<Integer, PollingQueryRunnable> map){
		
		Connection con =null;  
	    PreparedStatement pstmt = null;  
	    ResultSet rs = null;
		List<CustomInfo> tableList = null;
		try {
			con = JdbcUtil.getCon();
			String sql = "select u.id uId, u.username, u.phoneNumber,u.email,dbs.dbName,t.tableName, f.id fId, f.fieldName,et.typeName, ue.pollingTime, ue.timeSpan, ue.id ueId , efc.condition, efc.value from t_eventFieldsCondition efc, t_userEvent ue, t_fields f, t_user u, t_eventType et ,t_dbs dbs,t_tables t where ue.id = efc.ueId and ue.uId = u.id and ue.etId = et.id and efc.fId = f.id and dbs.id = t.dbId and t.id = f.tId";
			tableList = new ArrayList<CustomInfo>();
			 pstmt = con.prepareStatement(sql);
			 rs = pstmt.executeQuery();
			while(rs.next()){
				if(!map.containsKey(rs.getInt("ueId"))){
				CustomInfo ci = new CustomInfo();
				ci.setuId(rs.getInt("uId"));
				ci.setUeId(rs.getInt("ueId"));
				ci.setUsername(rs.getString("username"));
				ci.setPhoneNumber(rs.getString("phoneNumber"));
				ci.setDbName(rs.getString("dbName"));
				ci.setTableName(rs.getString("tableName"));
				ci.setEmail(rs.getString("email"));
				ci.setfId(rs.getInt("fId"));
				ci.setFieldName(rs.getString("fieldName"));
				ci.setEventType(rs.getString("typeName"));
				ci.setPollingTime(rs.getInt("pollingTime"));
				ci.setTimeSpan(rs.getInt("timeSpan"));
				ci.setCondition(rs.getString("condition"));
				ci.setValue(rs.getString("value"));
				
				tableList.add(ci);
			}}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return tableList;
	}
	
     
	public List<CustomInfo> getserchCustomInfo(String info){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		List<CustomInfo> tableList = null;
		try {
			con = JdbcUtil.getCon();
			String sql = " select u.id uId, u.username, u.phoneNumber,u.email,dbs.dbName,t.tableName, et.typeName, ue.pollingTime, "+
  " ue.timeSpan, ue.id ueId ,ue.tId tId, [desc],type from  t_userEvent ue,  t_user u, t_eventType et ,t_dbs dbs,t_tables t where "+
   " ue.uId = u.id and ue.etId = et.id and  dbs.id = t.dbId and u.username like '%"+info+"%' ";//desc是关键字，需要使用[]括起来
			tableList = new ArrayList<CustomInfo>();
			 pstmt = con.prepareStatement(sql);
			 rs = pstmt.executeQuery();
			 System.out.println(info);
			 System.out.println(sql);
			while(rs.next()){
				CustomInfo ci = new CustomInfo();
				ci.setDesc(rs.getString("desc"));
				ci.setuId(rs.getInt("uId"));
				ci.setUeId(rs.getInt("ueId"));
				ci.setUsername(rs.getString("username"));
				ci.setPhoneNumber(rs.getString("phoneNumber"));
				ci.setDbName(rs.getString("dbName"));
				ci.setTableName(rs.getString("tableName"));
				ci.setEmail(rs.getString("email"));
			//	ci.setfId(rs.getInt("fId"));
				ci.settId(rs.getInt("tId"));
			//	ci.setFieldName(rs.getString("fieldName"));
				ci.setEventType(rs.getString("typeName"));
				ci.setPollingTime((int)(rs.getInt("pollingTime")/1000.0));
				ci.setTimeSpan((int)(rs.getInt("timeSpan")/1000));
				ci.setType(rs.getString("type"));
			//	ci.setCondition(rs.getString("condition"));
			//	ci.setValue(rs.getFloat("value"));
				tableList.add(ci);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return tableList;
	}
	
	
	
}
